package com.wenda.homeschooedu.daoimpl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.wenda.homeschooedu.DB.DB;
import com.wenda.homeschooedu.DB.JDBCConnection;
import com.wenda.homeschooedu.dao.Iscore;
import com.wenda.homeschooedu.model.Score;

public class ScoreService implements Iscore {
	JDBCConnection jdbcConnection = null;
	Statement statement = null;

	public ScoreService() {
		jdbcConnection = new JDBCConnection();
		statement = jdbcConnection.getStm();
	}

	public List<Score> getScoresByClass(int class_Id, int year, int term,
			int exam_id, int orderBy) {

		ArrayList<Score> scores = new ArrayList<Score>();
		String order = "total";
		switch (orderBy) {
		case 0:
			order = "total";
			break;

		case 1:
			order = "chinese desc , total";
			break;
		case 2:
			order = "math desc , total";
			break;
		case 3:
			order = "english desc , total";
			break;

		default:
			order = "total";
			break;
		}

		String sql = String.format(DB.Table.Score.SQL.SELECT, class_Id,
				year, term, exam_id, order);
		System.out.println(sql);

		try {
			ResultSet resultSet = statement.executeQuery(sql);
			if (resultSet != null) {
				while (resultSet.next()) {
					Score score = new Score();
					score.set_id(resultSet.getInt(resultSet.findColumn("_id")));

					score.setStudent_id(resultSet.getInt(resultSet
							.findColumn("student_id")));

					score.setStudent_name(resultSet.getString(resultSet
							.findColumn("student_name")));
					score.setChineseScore(resultSet.getFloat(resultSet
							.findColumn("chinese")));
					score.setMathScore(resultSet.getFloat(resultSet
							.findColumn("math")));
					score.setEnglishScore(resultSet.getFloat(resultSet
							.findColumn("english")));
					score.setTotal(resultSet.getFloat(resultSet
							.findColumn("total")));
					scores.add(score);

				}

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			return null;
		}

		return scores;
	}

}
